package com.scalar.chiptrack.operations.tapereel.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;

import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.dao.ProductDao;
import com.scalar.chiptrack.operations.process.dao.NewVendorLocationDAO;
import com.scalar.chiptrack.operations.tapereel.TapeReelRequest;
import com.scalar.chiptrack.operations.tapereel.TapeReelRequestLot;
import com.scalar.chiptrack.operations.tapereel.ReelQty;

import com.scalar.chiptrack.operations.release.ReleaseHelper;
import com.scalar.chiptrack.operations.release.forms.ReleaseInfoSetupForm;
import com.scalar.chiptrack.operations.release.dao.RelaseDAO;

import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.Mailer;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.users.User;

/**
 * Created by IntelliJ IDEA.
 * User: Sudhakar
 * Date: Apr 14, 2007
 * Time: 3:30:13 PM
 */
public class TapeReelRequestDAO
{

	public static ArrayList loadTapeAndReelFgProducts() throws ScalarException
	{
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList partNumberList    = new ArrayList();
        
        try
		{
            conn = DataSource.getConnection();
        	pstmt = conn.prepareStatement( "{CALL SP_Load_TapeReel_FG( ) }" );
        	rs = pstmt.executeQuery();
        	while ( rs.next() )
            {
        		partNumberList.add( new LabelValueBean( rs.getString( "PART_NUMBER" ), rs.getString( "PROCESSSTEP_ID" ) ) );
            }
		}
        catch (Exception e)
		{
            e.printStackTrace();
            throw new ScalarException( "Could not get Tape and Reel Part Numbers", e );
		}
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        
        return partNumberList;
	}
	
    /**
     *
     * @param type
     * @return
     * @throws ScalarException
     */
    public static ArrayList getPartNumber(String type) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList partNumberList = null;
        String partNumber = null;
        String processStepId = null;


        try
        {
            partNumberList = new ArrayList();
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("SELECT PRODUCT.PART_NUMBER,PROCESS_STEP.PROCESSSTEP_ID, PROCESS_STEP.PS_TYPE  FROM PRODUCT,PROCESS_STEP \n" +
                    "WHERE PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID AND PS_TYPE_NAME = ? AND OBSOLETE != 0 ORDER BY PRODUCT.PART_NUMBER");

            pstmt.setString(1, type);
            rs = pstmt.executeQuery();
            while (rs.next())
            {
                partNumber      = rs.getString( "PART_NUMBER" );
                processStepId   = rs.getString( "PROCESSSTEP_ID" );
                partNumberList.add( new org.apache.struts.util.LabelValueBean( partNumber, processStepId ) );
            }
        }
        catch (Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException("Could not get Part Number by type", sql);
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
        return partNumberList;
    }

    public static ArrayList getTrLocations() throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList locationList = new ArrayList();
        ResultSet rs = null;
        String locationName = null;
        String locationId  = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT LOCATION_ID, L_VENDOR_NAME  FROM LOCATION WHERE L_TYPE = 'Finished Goods'");

            rs = pstmt.executeQuery();
            while (rs.next())
            {

                locationId = rs.getString("LOCATION_ID");
                locationName = rs.getString("L_VENDOR_NAME");
                locationList.add( new org.apache.struts.util.LabelValueBean( locationName, locationId ) );
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load all Locations from the database", sqle);
        }
        finally {
            if (rs != null) {
                try {
                    rs.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
        return locationList;
    }
    /**
     *
     * @param incrementBy
     * @return
     * @throws ScalarException
     */
    public static String genarateAutomatedTapeReelWORequestNumber(int incrementBy) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        String woReqNo          = null;
        String finalwoReqNo     = null;
        String number           = null;
        String woReqPrefix      = null;
        int woReqLength         = 0;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("sales.salesOrder.getSalesOrderInfo"));
            rs = pstmt.executeQuery();
            if (rs.next())
            {
                woReqPrefix = rs.getString("Shipping_Request_Prefix");
                woReqLength = rs.getInt("Shipping_Request_Length");
            }
            rs.close();
            pstmt.close();
            pstmt = null;

            if (conn == null || conn.isClosed())
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT Tape_Reel_Request_Id, Work_Order_NO FROM Tape_reel_request_table WHERE Tape_Reel_Request_Id = ( SELECT MAX( Tape_Reel_Request_Id ) FROM Tape_reel_request_table )");
            rs = pstmt.executeQuery();
            if (rs.next())
            {
                woReqNo = rs.getString("Work_Order_NO");
            }
            else
            {
                woReqNo = "0";
            }

            try
            {
                if (woReqNo != null)
                {
                    int indexValue = woReqNo.indexOf("-");
                    number = woReqNo.substring(indexValue + 1);
                    String no = (Long.parseLong(number) + incrementBy) + "";
                    int noLength = no.length();
                    int totalLength = woReqLength - noLength;
                    if (totalLength > 0)
                    {
                        for (int i = 0; i < totalLength; i++)
                        {
                            no = "0" + no;
                        }
                    }

                    if (woReqPrefix != null && woReqPrefix.trim().length() != 0)
                    {
                        finalwoReqNo = woReqPrefix + "-" + no;
                    }
                    else
                    {
                        finalwoReqNo = no;
                    }
                }
            }
            catch (NumberFormatException nfe)
            {
                finalwoReqNo = (rs.getInt("Tape_Reel_Request_Id") + incrementBy) + "";
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException("Could not get Automated Tape and Reel Request WO No from database", sqlex);
        }

        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException excp) {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException excp) {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException excp) {
                }
            }
        }
        return finalwoReqNo;
    }

    /**
     *
     * @param locationId
     * @return
     * @throws ScalarException
     */
    public static String getVendorName(int locationId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String vendorName = null;
        ResultSet rset = null;

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("SELECT L_VENDOR_NAME From LOCATION WHERE LOCATION_ID = ?");
            pstmt.setInt(1, locationId);
            rset = pstmt.executeQuery();

            while (rset.next()) {
                vendorName = rset.getString("L_VENDOR_NAME");
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load all Lotnumbers from the database", sqle);
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
        return vendorName;
    }    
    /**
     *
     * @param processStep_id
     * @param key
     * @return
     * @throws ScalarException
     */
    public static String getProcessParameter(int processStep_id, String key) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        String paramValue       = null;
        ResultSet rset          = null;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement("Select PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ? " +
                    "AND PROCESSSTEP_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID(?) \n" +
                    "where PS_Type = 'Assembly' )");

            pstmt.setString(1, key);
            pstmt.setInt(2, processStep_id);
            rset = pstmt.executeQuery();

            while (rset.next())
            {
                paramValue = rset.getString("PARAMETER_VALUE");
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not get "+ key + " from the database", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try
                {
                    rset.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
        return paramValue;
    }
    /**
     *
     * @param tapeReelRequest
     * @return
     * @throws ScalarException
     */
    public static ArrayList getFGLotDetails(TapeReelRequest tapeReelRequest) throws ScalarException
    {
        ArrayList tapeReelLotsList = new ArrayList();
        TapeReelRequestLot tapeReelRequestLot = null;
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;

        try
        {

            conn = DataSource.getConnection();
            cstmt = cstmt = conn.prepareCall( "{ call sp_load_fg_lots_for_search( ?,?,? ) }" );
            cstmt.setInt(1, tapeReelRequest.getTrProcessStepId());
            cstmt.setString(2, tapeReelRequest.getVendor());
            cstmt.setInt(3, tapeReelRequest.getTrRequestid());
            rs = cstmt.executeQuery();

            while (rs.next())
            {
                tapeReelRequestLot = new TapeReelRequestLot();
                tapeReelRequestLot.setLotId(rs.getInt("Lot_Id"));
                tapeReelRequestLot.setPartNumber(rs.getString("PART_NUMBER"));
                tapeReelRequestLot.setLotNumber(rs.getString("LOT_NUMBER"));
                tapeReelRequestLot.setAvailableQty(rs.getInt("qty_available"));
                tapeReelRequestLot.setPulledQty( rs.getInt( "Qty_Pulled" ) );
                tapeReelRequestLot.setTrQty( rs.getInt( "TR_Qty" ) );
                tapeReelRequestLot.setReturnQty( rs.getInt( "qty_return" ) );

                if( tapeReelRequestLot.getPulledQty() > 0 )
                {
                    tapeReelRequestLot.setChecked( true );
                }
                tapeReelRequestLot.setRejectQty( rs.getInt( "Qty_rejected" ) );

                tapeReelLotsList.add(tapeReelRequestLot);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            Logger.error("Error while retreiving the existing Tape Reel lots", e);
            throw new ScalarException("Error while retreiving the existing Tape Reel lots");
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }

            if (cstmt != null)
            {
                try
                {
                    cstmt.close();
                } catch (SQLException ignore) {
                }
            }

            if (conn != null)
            {
                try
                {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
        return tapeReelLotsList;
    }

    /**
     *
     * @param dbTransaction
     * @param tapeReelRequest
     * @throws ScalarException
     */
    public static void saveTapeReelRequestDetails(DBTransaction dbTransaction, TapeReelRequest tapeReelRequest) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall("{ call SP_SAVE_TAPE_REEL_REQUEST_DETAILS( ?,?,?,?,?,?,?,?,?,?,?,?,?,? ) }");
            cstmt.setInt(1, tapeReelRequest.getTrRequestid());
            cstmt.setString(2, tapeReelRequest.getWorkOrderNo());
            cstmt.setString(3, tapeReelRequest.getPoNumber());
            cstmt.setString(4, tapeReelRequest.getReelQty());
            cstmt.setString(5, tapeReelRequest.getReqQty());
            cstmt.setString(6, tapeReelRequest.getReqDate());
            cstmt.setString(7, tapeReelRequest.getExpCompDate());
            cstmt.setString(8, tapeReelRequest.getActualCompDate());
            cstmt.setInt(9, tapeReelRequest.getTrProcessStepId());
            cstmt.setInt(10, tapeReelRequest.getTrlocationId());
            cstmt.setString(11, tapeReelRequest.getComments());
            cstmt.setString(12, tapeReelRequest.getSpecialInstructions());
            cstmt.setString(13, tapeReelRequest.getReqStatus());
            cstmt.registerOutParameter(14, Types.INTEGER);

            cstmt.executeUpdate();
            tapeReelRequest.setTrRequestid(cstmt.getInt(14));
            success = true;
        }
        catch (Exception e)
        {
            success = false;
            e.printStackTrace();
            Logger.error("Error while saving the Tape & Reel Request details", e);
            throw new ScalarException("Error while saving the Tape & Reel Request details");
        }
        finally
        {
            if (dbTransaction == null)
            {
                if (success == true)
                {
                    try
                    {
                        conn.commit();
                    }
                    catch (Exception e)
                    {
                        throw new ScalarException("Errow while commiting the Tape & Reel Request details Confirm");
                    }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch (Exception e)
                    {
                        throw new ScalarException("Errow while rolling back the Tape & Reel Request details Confirm");
                    }
                }
            }

            if (cstmt != null)
            {
                try
                {
                    cstmt.close();
                } catch (SQLException ignore) {
                }
            }

            if (conn != null)
            {
                try
                {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }

    /**
     *
     * @param dbTransaction
     * @param tapeReelRequest
     * @throws ScalarException
     */
    public static void saveTapeReelRequestLotDetails(DBTransaction dbTransaction, TapeReelRequest tapeReelRequest) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        boolean success         = false;
        Iterator tapeReelReqLotListIterator = tapeReelRequest.getLotList().iterator();
        TapeReelRequestLot tapeReelRequestLot = null;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall( "{ call sp_save_tape_reel_request_lot_details( ?,?,?,?,?,?,?,?,?,?,?,? ) }" );

            while( tapeReelReqLotListIterator.hasNext() )
            {
                tapeReelRequestLot = ( TapeReelRequestLot ) tapeReelReqLotListIterator.next();

            	cstmt.setInt    ( 1, tapeReelRequest.getTrRequestid() );
            	cstmt.setString ( 2, tapeReelRequestLot.getLotNumber() );
            	cstmt.setInt    ( 3, tapeReelRequestLot.getLotId() );
            	cstmt.setInt    ( 4, tapeReelRequestLot.getPulledQty() );
            	cstmt.setInt    ( 5, tapeReelRequestLot.getAvailableQty() );
            	cstmt.setInt    ( 6, tapeReelRequestLot.getTrQty() );
            	cstmt.setInt    ( 7, tapeReelRequestLot.getReturnQty() );
            	cstmt.setInt	( 8, tapeReelRequestLot.getRejectQty() );
            	cstmt.setString ( 9, tapeReelRequest.getReqDate() );
            	cstmt.setInt    ( 10, tapeReelRequest.getTrProcessStepId() );
            	cstmt.setInt    ( 11, tapeReelRequest.getTrlocationId() );
            	cstmt.setBoolean( 12, !tapeReelRequestLot.isCancelled() );

            	cstmt.executeUpdate();
            }

            success = true;
        }
        catch (Exception e)
        {
            e.printStackTrace();
            Logger.error("Error while saving the Tape & Reel Request lot details", e);
            throw new ScalarException("Error while saving the Tape & Reel Request lot details");
        }
        finally
        {
            if (dbTransaction == null)
            {

                if (success == true)
                {
                    try
                    {
                        conn.commit();
                    }
                    catch (Exception e)
                    {
                        throw new ScalarException("Errow while commiting the Tape & Reel Request lot details save");
                    }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch (Exception e)
                    {
                        throw new ScalarException("Errow while rolling back the Tape & Reel Request lot details save");
                    }
                }
            }

            if (cstmt != null)
            {
                try
                {
                    cstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    /**
     *
     * @param tapeReelRequest
     * @return
     * @throws ScalarException
     */
    public static ArrayList getAllTapeReelRequestDetails(TapeReelRequest tapeReelRequest) throws ScalarException
    {
        ArrayList tapeReelLotsList = new ArrayList();
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;

        try
        {
            conn = DataSource.getConnection();
            cstmt = cstmt = conn.prepareCall("{ call SP_Search_Tape_Reel_Requests( ?,?,?,?,? ) }");
            cstmt.setInt(1, tapeReelRequest.getTrProcessStepId());
            cstmt.setInt(2, tapeReelRequest.getTrlocationId());
            cstmt.setString(3, tapeReelRequest.getReqStatus());
            cstmt.setString(4, tapeReelRequest.getFromDate());
            cstmt.setString(5, tapeReelRequest.getToDate());
            rs = cstmt.executeQuery();

            while (rs.next())
            {
                tapeReelRequest = new TapeReelRequest();
                tapeReelRequest.setWorkOrderNo(rs.getString("WORK_ORDER_NO"));
                tapeReelRequest.setTrPartNumber(rs.getString("PART_NUMBER"));
                tapeReelRequest.setVendor(rs.getString("L_VENDOR_NAME"));
                tapeReelRequest.setReqQty(rs.getString("REQ_QTY"));
                tapeReelRequest.setReqDate(rs.getString("REQ_DATE"));
                tapeReelRequest.setTrRequestid(rs.getInt("Tape_Reel_Request_Id"));
                tapeReelRequest.setReqStatus(rs.getString("Status"));
                tapeReelRequest.setBuyIn( rs.getInt( "Buy_In" ) );
                tapeReelRequest.setQtyPulled(rs.getString("QtyPulled") );
                tapeReelLotsList.add(tapeReelRequest);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            Logger.error("Error while retreiving the existing Tape Reel Details", e);
            throw new ScalarException("Error while retreiving the existing Tape Reel Details");
        }
        finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (cstmt != null)
            {
                try
                {
                    cstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
        return tapeReelLotsList;
    }

    /**
     *
     * @param dbTransaction
     * @param tapeReelRequest
     * @return
     * @throws ScalarException
     */
    public static TapeReelRequest loadTapeReelRerquestDetails(DBTransaction dbTransaction, TapeReelRequest tapeReelRequest) throws ScalarException
    {
        TapeReelRequest tempTapeReelRequest = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("SELECT TR.TAPE_REEL_REQUEST_ID,TR.WORK_ORDER_NO, TR.PO_NUMBER, TR.REEL_QTY, TR.REQ_QTY, TR.REQ_DATE, TR.EXP_COMPLETE_DATE, TR.ACTUAL_COMP_DATE,\n" +
                    "TR.TR_PROCESSSTEP_ID, TR.TR_LOCATION_ID, TR.PACKAGE, TR.PIN_COUNT,TR.COMMENTS, TR.SPECIAL_INSTRUCTIONS,TR.STATUS, TR.FG_PROCESSSTEP_ID, P.PART_NUMBER, L.L_VENDOR_NAME, L.VENDOR_ID \n" +
                    "FROM TAPE_REEL_REQUEST_TABLE TR, PRODUCT P, LOCATION L WHERE TR.TR_PROCESSSTEP_ID = P.PROCESSSTEP_ID \n" +
                    "AND L.LOCATION_ID = TR.TR_LOCATION_ID AND TR.TAPE_REEL_REQUEST_ID = ? ");

            pstmt.setInt(1, tapeReelRequest.getTrRequestid());
            rset = pstmt.executeQuery();

            if (rset.next())
            {
                tempTapeReelRequest = new TapeReelRequest();
                tempTapeReelRequest.setTrRequestid(rset.getInt("TAPE_REEL_REQUEST_ID"));
                tempTapeReelRequest.setWorkOrderNo(rset.getString("WORK_ORDER_NO"));
                tempTapeReelRequest.setPoNumber(rset.getString("PO_NUMBER"));
                tempTapeReelRequest.setReelQty(rset.getString("REEL_QTY"));
                tempTapeReelRequest.setReqQty(rset.getString("REQ_QTY"));
                tempTapeReelRequest.setReqDate(StringUtils.formatDateToString(rset.getDate("REQ_DATE"), "MM/dd/yyyy"));
                tempTapeReelRequest.setExpCompDate(StringUtils.formatDateToString(rset.getDate("EXP_COMPLETE_DATE"), "MM/dd/yyyy"));
                tempTapeReelRequest.setActualCompDate(StringUtils.formatDateToString(rset.getDate("ACTUAL_COMP_DATE"), "MM/dd/yyyy"));
                tempTapeReelRequest.setTrProcessStepId(rset.getInt("TR_PROCESSSTEP_ID"));
                tempTapeReelRequest.setTrlocationId(rset.getInt("TR_LOCATION_ID"));
                tempTapeReelRequest.setPackageType(rset.getString("PACKAGE"));
                tempTapeReelRequest.setPinCount(rset.getString("PIN_COUNT"));
                tempTapeReelRequest.setComments(rset.getString("COMMENTS"));
                tempTapeReelRequest.setSpecialInstructions(rset.getString("SPECIAL_INSTRUCTIONS"));
                tempTapeReelRequest.setReqStatus(rset.getString("STATUS"));
                tempTapeReelRequest.setFgprocessStepId(rset.getInt("FG_PROCESSSTEP_ID"));
                tempTapeReelRequest.setTrPartNumber(rset.getString("PART_NUMBER"));
                tempTapeReelRequest.setVendor(rset.getString("L_VENDOR_NAME"));
                tempTapeReelRequest.setVendorId( rset.getInt("VENDOR_ID") );
            }

        }
        catch (Exception e)
        {
            Logger.error("Error while retreiving the Tape & Reel request details", e);
            e.printStackTrace();
            throw new ScalarException("Error while retreiving the Tape & Reel request details");
        }
        finally
        {
            if (rset != null)
            {
                try
                {
                    rset.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }

        /*tempTapeReelRequest.setVendorContactPersonsList( NewVendorLocationDAO.loadVendorContactPersonsList( null,  tempTapeReelRequest.getVendorId(), Constants.LOCATION_TYPE_FINISHED_GOODS , -1 ) );
        tempTapeReelRequest.setTrRequestAlerts( ReleaseHelper.getContactPersonsAsString( "email", tempTapeReelRequest.getVendorContactPersonsList()  ) );
        */
        return tempTapeReelRequest;
    }

    public static void cancelTapeReeelRequest( DBTransaction dbTransaction , TapeReelRequest tapeReelRequest ) throws ScalarException
    {
        Connection conn 		= null;
        CallableStatement cstmt = null;
        boolean success = false;
        Iterator cancelshipReqLotListIterator = tapeReelRequest.getLotList().iterator();
        TapeReelRequestLot tapeReelRequestLot = null;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            cstmt = conn.prepareCall( "{ call sp_cancel_tape_reel_request_details( ?, ?, ?, ?, ?, ? ) }" );
            while( cancelshipReqLotListIterator.hasNext() )
            {
                tapeReelRequestLot = ( TapeReelRequestLot )cancelshipReqLotListIterator.next();

                cstmt.setInt( 1, tapeReelRequest.getTrRequestid() );
                cstmt.setInt( 2, tapeReelRequestLot.getLotId() );
                cstmt.setInt( 3, tapeReelRequestLot.getPulledQty() );
                cstmt.setString( 4, tapeReelRequest.getVendor() );
                cstmt.setInt( 5, tapeReelRequest.getTrProcessStepId() );
                cstmt.setString( 6, tapeReelRequestLot.getLotNumber() );

                cstmt.executeUpdate();
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            Logger.error( "Error while Cancel the Tape & Reel Request lot details", e );
            throw new ScalarException ("Error while Cancel the Tape & Reel Request lot details");
        }
        finally
        {
            if ( dbTransaction == null )
            {

                if( success == true )
                {
                    try
                    {
                        conn.commit();
                    }
                    catch( Exception e )
                    {
                        throw new ScalarException( "Errow while commiting the Tape & Reel Request lot details save" );
                    }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch( Exception e )
                    {
                        throw new ScalarException( "Errow while rolling back the Tape & Reel Request lot details save" );
                    }
                }
            }

            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }

    /**
     *
     * @param dbTransaction
     * @param tapeReelRequest
     * @throws ScalarException
     */
    public static void updateShipRequestStatus(DBTransaction dbTransaction, TapeReelRequest tapeReelRequest) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("UPDATE Tape_Reel_Request_Table SET STATUS = ? WHERE Tape_Reel_Request_Id = ?");
            pstmt.setString(1, tapeReelRequest.getReqStatus());
            pstmt.setInt(2, tapeReelRequest.getTrRequestid());
            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception e)
        {
            e.printStackTrace();
            Logger.error("Error while updating the Tape & Reel Request status", e);
            throw new ScalarException("Error while updating the Tape & Reel Request status");
        }
        finally
        {
            if (dbTransaction == null)
            {
                if (success == true)
                {
                    try
                    {
                        conn.commit();
                    }
                    catch (Exception e)
                    {
                        throw new ScalarException("Errow while commiting the Tape & Reel Request details save");
                    }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch (Exception e)
                    {
                        throw new ScalarException("Errow while rolling back the Tape & Reel Request details save");
                    }
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }

    /**
     *
     * @param dbTransaction
     * @param tapeReelRequest
     * @throws ScalarException
     
    public static ArrayList loadTapeReelAlertMails ( DBTransaction dbTransaction, TapeReelRequest tapeReelRequest ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        StringBuffer emails = new StringBuffer();
        ArrayList vendorContactPersonsList = new ArrayList();
        ContactPerson contactPerson = null;
        boolean success = false;

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "select Contact_Person, Email from Address where Address_Id in ( select Address_Id from VENDOR_CONTACT_PERSONS Where Vendor_ID = ( select Vendor_Id from Location where Location_ID = ?  ) )" );
            pstmt.setInt( 1, tapeReelRequest.getTrlocationId() );

            rset = pstmt.executeQuery();
            while( rset.next() )
            {
                contactPerson = new ContactPerson();
                contactPerson.setContactPerson(rset.getString( "Contact_person"));
                contactPerson.setEmail(rset.getString( "Email"));
                emails.append( rset.getString( "EMAIL") ).append( Mailer.EMAIL_SEPARATOR );
                vendorContactPersonsList.add(contactPerson);
            }

            tapeReelRequest.setTrRequestAlerts( emails.toString().replaceAll( ",,", ",") );
            success = true;
        }
        catch (Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not fetch Tape & Reel Request alert mails", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                           conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException ignore ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch( SQLException ignore ){}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){}
            }
        }
        return vendorContactPersonsList;
    }*/
    /**
     *
     * @param dbTransaction
     * @param tapeReelRequestId
     * @return
     * @throws ScalarException
     */
    public static TapeReelRequest getTapeReelRequest(DBTransaction dbTransaction, int tapeReelRequestId) throws ScalarException
    {
        TapeReelRequest tapeReelRequest = new TapeReelRequest();
        tapeReelRequest.setTrRequestid( tapeReelRequestId );
        tapeReelRequest = TapeReelRequestDAO.loadTapeReelRerquestDetails(dbTransaction, tapeReelRequest);
        // Load FG Part Number
        tapeReelRequest.setFgPartNumber(ProductDao.getProductNameByProcessStep_Id(tapeReelRequest.getTrProcessStepId()));
        // Load Pin and Package
        tapeReelRequest.setPinCount( TapeReelRequestDAO.getProcessParameter( tapeReelRequest.getTrProcessStepId(), "PIN" ));
        tapeReelRequest.setPackageType( TapeReelRequestDAO.getProcessParameter( tapeReelRequest.getTrProcessStepId(), "PACKAGE ID" ));
        // Load Lot Details
        TapeReelRequestDAO.loadTapeReelRequestLotDetails(dbTransaction, tapeReelRequest);
        
        if(tapeReelRequest.getActualCompDate() == null )
        {
            tapeReelRequest.setActualCompDate("");
        }
        if(tapeReelRequest.getExpCompDate() == null )
        {
            tapeReelRequest.setExpCompDate("");
        }
        return tapeReelRequest;
    }

    /**
     *
     * @param dbTransaction
     * @param tapeReelRequest
     * @throws ScalarException
     */
    public static void loadTapeReelRequestLotDetails( DBTransaction dbTransaction, TapeReelRequest tapeReelRequest ) throws ScalarException
    {
        ArrayList tapeReelRequestLotList        = new ArrayList();
        Connection conn                         = null;
        PreparedStatement pstmt                 = null;
        ResultSet rset                          = null;
        TapeReelRequestLot tapeReelRequestLot   = null;

        try
        {
            if( dbTransaction == null )
            {
                conn	= DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt 	= conn.prepareStatement( "SELECT Tape_reel_Request_id, TRLT.LOT_ID, LT.LOT_NUMBER, TRLT.QTY_PULLED,TRLT.AVAILABLE_QTY, TRLT.TR_QTY, TRLT.Return_Qty, TRLT.Reject_Qty \n" +
                    "FROM Tape_reel_REQUEST_LOT_TABLE TRLT, LOT_TABLE LT WHERE TRLT.LOT_ID = LT.LOT_ID AND Tape_reel_Request_id = ?" );
            pstmt.setInt( 1, tapeReelRequest.getTrRequestid() );
            rset = pstmt.executeQuery();
            int totalTrQty = 0;
            int trQty   = 0;
            while ( rset.next() )
            {
               tapeReelRequestLot  = new TapeReelRequestLot();
               tapeReelRequestLot.setLotId( rset.getInt("LOT_ID") );
               tapeReelRequestLot.setLotNumber( rset.getString("LOT_NUMBER") );
               tapeReelRequestLot.setPulledQty( rset.getInt("QTY_PULLED") );
               tapeReelRequestLot.setAvailableQty( rset.getInt("AVAILABLE_QTY") );
               tapeReelRequestLot.setTrQty( rset.getInt("TR_QTY") );
               tapeReelRequestLot.setReturnQty( rset.getInt("Return_Qty") );
               tapeReelRequestLot.setRejectQty( rset.getInt("Reject_Qty") );
               tapeReelRequestLot.setChecked( true );

               trQty = tapeReelRequestLot.getTrQty();

               totalTrQty = totalTrQty + trQty;
               tapeReelRequestLotList.add( tapeReelRequestLot );
            }

            tapeReelRequest.setLotList( tapeReelRequestLotList );
            tapeReelRequest.setTotalTrQty( totalTrQty );

        }
        catch( Exception e )
        {
            Logger.error( "Error while retreiving the Tape & Reel request details", e );
            e.printStackTrace();
            throw new ScalarException ("Error while retreiving the Tape & Reel request details");
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }


    }

    /**
     *
     * @param tapeReelRequest
     * @throws Exception
     */
    public static void sendTapeReelRequestMail(TapeReelRequest tapeReelRequest, User user) throws Exception
    {
        TapeReelRequestLot tapeReelRequestLot = null;
        String 		toMails = null;
        String      ccMails = null;
        ReleaseInfoSetupForm releaseInfoSetupForm   = new ReleaseInfoSetupForm();

        try
        {
            releaseInfoSetupForm.setVendorId(tapeReelRequest.getVendorId());
            releaseInfoSetupForm.setCategory("PROD");
            RelaseDAO.loadReleaseInfoSetup("Class Store", releaseInfoSetupForm);

            toMails = loadVendorContactPersonsEmails( null, releaseInfoSetupForm.getReleaseInfoSetupId() ) ;
            ccMails = loadContactPersonsEmails( null, releaseInfoSetupForm.getReleaseInfoSetupId() ) ;

        }
        catch (Exception se) 
		{
            se.printStackTrace();
            System.out.println("Exception while retrieving email list");
        }
        
        /*String from = ConfigManager.getScalarAdminEmail();
        int index = from.lastIndexOf("\\");
        from = from.substring(index + 1, from.length());*/
        
        // FORMAT MESSAGE
        StringBuffer buf = new StringBuffer(2098);
        buf.append("<HTML><HEAD></HEAD><BODY>");
        buf.append("<TABLE ALIGN = 'CENTER' style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='0' cellPadding='0' width='70%' border=1>");
        buf.append("<TR bgColor='#ccccff'><TD colspan='4' width='100%' align='center' ><B> Tape & Reel Request <B></TD></tr>");
        buf.append("<tr><td><TABLE ALIGN = 'CENTER'style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='100%' border=0>");
        buf.append("<tr bgColor='#e6e6e6'>\n" +
                "\t<TD class=\"NArialL\" width='20%'>&nbsp; <B>WO</B></TD>\n" +
                "\t<TD class=\"NArialL\" width='30%'>&nbsp;" + tapeReelRequest.getWorkOrderNo() + "</TD>\n" +
                "\t<TD class=\"NArialL\" width='20%'>&nbsp;<B>PO</B></TD>\n" +
                "\t<TD class=\"NArialL\" width='30%'>&nbsp;" + tapeReelRequest.getPoNumber() + "</TD>\n" +
                "</tr>");
        buf.append("<tr bgColor='#e6e6e6'>\n" +
                "\t<TD class=\"NArialL\" >&nbsp; <B>TR PartNumber</B></TD>\n" +
                "\t<TD class=\"NArialL\">&nbsp; " + tapeReelRequest.getTrPartNumber() + "</TD>\n" +
                "\t<TD class=\"NArialL\">&nbsp;<B>Vendor</B></TD>\n" +
                "\t<TD class=\"NArialL\">&nbsp;" + tapeReelRequest.getVendor() + "</TD>\n" +
                "</tr>");
        buf.append("<tr bgColor='#e6e6e6'>\n" +
                "\t<TD class=\"NArialL\" >&nbsp; <B>Package</B></TD>\n" +
                "\t<TD class=\"NArialL\">&nbsp;" + tapeReelRequest.getPackageType() + "</TD>\n" +
                "\t<TD class=\"NArialL\">&nbsp;<B>Pin Count</B></TD>\n" +
                "\t<TD class=\"NArialL\">&nbsp;" + tapeReelRequest.getPinCount() + "</TD>\n" +
                "</tr>");
        buf.append("<tr bgColor='#e6e6e6'>\n" +
                "\t<TD class=\"NArialL\" >&nbsp;<B> Reel Qty</B></TD>\n" +
                "\t<TD class=\"NArialL\">&nbsp;" + tapeReelRequest.getReelQty() + "</TD>\n" +
                "\t<TD class=\"NArialL\">&nbsp;</TD>\n" +
                "\t<TD class=\"NArialL\">&nbsp;</TD>\n" +
                "</tr>");
        buf.append("<tr bgColor='#e6e6e6'>\n" +
                "\t<TD class=\"NArialL\" >&nbsp; <B>Req Qty</B></TD>\n" +
                "\t<TD class=\"NArialL\">&nbsp;" + tapeReelRequest.getReqQty() + "</TD>\n" +
                "\t<TD class=\"NArialL\" >&nbsp;<B>Req Date</B></TD>\n" +
                "\t<TD class=\"NArialL\">&nbsp;" + tapeReelRequest.getReqDate() + "</TD>\n" +
                "</tr>");
        buf.append("<tr bgColor='#e6e6e6'>\n" +
                "\t<TD class=\"NArialL\" >&nbsp; <B>Exp Comp Date</B></TD>\n" +
                "\t<TD class=\"NArialL\">&nbsp;" + tapeReelRequest.getExpCompDate() + "</TD>\n" +
                "\t<TD class=\"NArialL\">&nbsp;<B>Actual Comp Date</B></TD>\n" +
                "\t<TD class=\"NArialL\">&nbsp;" + tapeReelRequest.getActualCompDate() + "</TD>\n" +
                "</tr>");
        buf.append("</table></td></tr>");
        buf.append("<tr><td><table style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='100%' border=0>");
        buf.append("<tr bgColor='#e6e6e6'>\n" +
                "\t<TD class=\"NArialL\" width='20%'>&nbsp; <B>Special Instructions</B></TD>\n" +
                "\t<TD class=\"NArialL\" width='80%' colspan ='4'>&nbsp;" + tapeReelRequest.getSpecialInstructions() + "</TD>\t\n" +
                "</tr>");
        buf.append("<tr bgColor='#e6e6e6'>\n" +
                "\t<TD class=\"NArialL\" >&nbsp; <B>Comments</B></TD>\n" +
                "\t<TD class=\"NArialL\" colspan ='4'>&nbsp;" + tapeReelRequest.getComments() + "</TD>\t\n" +
                "</tr>");
        buf.append("</table></td></tr>");

        if( tapeReelRequest.getLotList().size() > 0 )
        {

            buf.append("<tr><td><table style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='100%' border=0>");
            buf.append("<tr bgColor='#e6e6e6'>\n" +
                    "\t<TD colspan = '6' class=\"NArialL\" >&nbsp;&nbsp;&nbsp;<B>Lot Info</B></TD>\n" +
                    "</tr>");
            buf.append("<tr bgColor='#e6e6e6'>\n" +
                    "\t<TD class=\"NArialL\"  width='30%' align='CENTER'>&nbsp;<B>FG Part Number</B></TD>\n" +
                    "\t<TD class=\"NArialL\"  width='30%' align='CENTER'>&nbsp;<B>Lot Number</B></TD>\n" +
                    "\t<TD class=\"NArialL\"  width='10%' align='CENTER'>&nbsp;<B>Qty Pulled</B></TD>\n" +
                    "\t<TD class=\"NArialL\"  width='10%' align='CENTER'>&nbsp;<B>T&R Qty</B></TD>\n" +
					"\t<TD class=\"NArialL\"  width='10%' align='CENTER'>&nbsp;<B>Return Qty</B></TD>\n" +
					"\t<TD class=\"NArialL\"  width='10%' align='CENTER'>&nbsp;<B>Reject Qty</B></TD>\n" +
                    "</tr>");

            int totalPulledQty = 0;
            int totalTRQty	= 0;
            int totalReturnQty = 0;
            int totalRejectQty = 0;
            
            for (int i = 0; i < tapeReelRequest.getLotList().size(); i++)
            {
                tapeReelRequestLot = (TapeReelRequestLot) tapeReelRequest.getLotList().get(i);

                buf.append("<tr bgColor='#e6e6e6'></TD><TD class='NArialL' width='30%' align='CENTER'>" + tapeReelRequest.getFgPartNumber() + "</TD>");
                buf.append("<TD class='NArialL' width='30%' align='CENTER'>" + tapeReelRequestLot.getLotNumber() + "</TD>");
                buf.append("<TD class='NArialL' width='10%' align='CENTER'>" + tapeReelRequestLot.getPulledQty() + "</TD>");
                buf.append("<TD class='NArialL' width='10%' align='CENTER'>" + tapeReelRequestLot.getTrQty() + "</TD>");
                buf.append("<TD class='NArialL' width='10%' align='CENTER'>" + tapeReelRequestLot.getReturnQty() + "</TD>");
                buf.append("<TD class='NArialL' width='10%' align='CENTER'>" + tapeReelRequestLot.getRejectQty() + "</TD></TR>");
                totalPulledQty += tapeReelRequestLot.getPulledQty();
                totalTRQty += tapeReelRequestLot.getTrQty();
                totalReturnQty += tapeReelRequestLot.getReturnQty();
                totalRejectQty += tapeReelRequestLot.getRejectQty();
            }
            buf.append("<tr bgColor='#e6e6e6'>\n" +
                    "\t<TD class=\"NArialL\"  width='30%' align='CENTER'>&nbsp;</TD>\n" +
                    "\t<TD class=\"NArialL\"  width='30%' align='CENTER'>&nbsp;<B>Running Total</B></TD>\n" +
                    "\t<TD class=\"NArialL\"  width='10%' align='CENTER'>&nbsp;"+ totalPulledQty + "</TD>\n" +
                    "\t<TD class=\"NArialL\"  width='10%' align='CENTER'>&nbsp;" + totalTRQty + "</TD>\n" +
					"\t<TD class=\"NArialL\"  width='10%' align='CENTER'>&nbsp;" + totalReturnQty + "</TD>\n" +
					"\t<TD class=\"NArialL\"  width='10%' align='CENTER'>&nbsp;" + totalRejectQty + "</TD>\n" +
                    "</tr></table></td></tr>");
        }
        buf.append("</TABLE></BODY></HTML>\n");
        String message = buf.toString();
        String subject = null;
        try
        {
            // SEND MESSAGE
            subject = "Tape & Reel Request " + tapeReelRequest.getWorkOrderNo() +" has been  " + tapeReelRequest.getReqStatus() + " from " + user.getFirstName();
            //Mailer.send(from, toMails, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false);
            Mailer.send ( user.getEmail(), toMails, ccMails, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
        }
        catch (ScalarException e) {
            throw new ScalarException("Exception while sending new Tape & Reel Request Acknowledgment mail notifaction");
        }

    }

    /**
     *
     * @param dbTransaction
     * @param tapeReelRequest
     * @throws ScalarException
     */
    public static void updateTapeReelRequestStatus(DBTransaction dbTransaction, TapeReelRequest tapeReelRequest) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("UPDATE Tape_Reel_REQUEST_TABLE SET STATUS = ?,  Time_Stamp = CURRENT_TIMESTAMP WHERE Tape_Reel_Request_Id = ?");
            pstmt.setString(1, tapeReelRequest.getReqStatus());
            pstmt.setInt(2, tapeReelRequest.getTrRequestid());
            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception e)
        {
            e.printStackTrace();
            Logger.error("Error while updating the Tape & Reel Request status", e);
            throw new ScalarException("Error while updating the Tape & Reel Request status");
        }
        finally
        {
            if (dbTransaction == null)
            {
                if (success == true)
                {
                    try
                    {
                        conn.commit();
                    }
                    catch (Exception e) {
                        throw new ScalarException("Errow while commiting the Tape & Reel Request details save");
                    }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch (Exception e) {
                        throw new ScalarException("Errow while rolling back the Tape & Reel Request details save");
                    }
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }

	/**
	 * @param trProcessStepId
	 * @return String
	 */
	public static String getReelQty(int trProcessStepId) throws ScalarException
	{
		String reelQty = "";
		Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        
        try
		{
            conn = DataSource.getConnection();
        	pstmt = conn.prepareStatement( "{CALL SP_GET_REEL_QTY( ? ) }" );
        	pstmt.setInt( 1, trProcessStepId );
        	rs = pstmt.executeQuery();
        	if( rs.next() )
            {
        		reelQty = rs.getString( "REEL_QTY" );
            }
		}
        catch (Exception e)
		{
            e.printStackTrace();
            throw new ScalarException( "Could not get Reel Qty", e );
		}
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
		
		return reelQty;
	}

   public static ArrayList loadReelQtys( DBTransaction dbTransaction,  String packageString, String pinNumber   ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        ArrayList reelQtyList = null;
        try
        {
            reelQtyList = new ArrayList();
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareCall( "{ call SP_GET_REEL_QTY_DETAILS(?,?) }" );
            pstmt.setString (1, packageString);
            pstmt.setInt(2, Integer.parseInt(pinNumber));
            rs = pstmt.executeQuery();
            ReelQty reelQty;
            while ( rs.next() )
            {
              reelQty = new ReelQty();
              reelQty.setPackageName (rs.getString("PACKAGENAME"));
              reelQty.setPinCount(rs.getString("PINNUMBER"));
              reelQty.setReelQty(rs.getInt("REELQTY"));
              reelQty.setExist( true );
              reelQtyList.add( reelQty );
            }
        }
        catch ( SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not get REEL QUANTITY Details ", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return reelQtyList ;
    }

    public static void saveReelQty ( DBTransaction dbTransaction, ArrayList reelQtyList ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        ResultSet rset = null;
        if (reelQtyList == null)
        {
            return;
        }
        try
        {
             if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            for(int i=0; i<reelQtyList.size() ; i++)
            {
                ReelQty reelQty= (ReelQty) reelQtyList.get(i);
                if (reelQty.isExist())
                {
                    pstmt=conn.prepareStatement( "UPDATE REEL_QTY_TABLE SET REEL_QTY = ? WHERE  PACKAGE= ? AND PIN_COUNT= ?" );
                     pstmt.setInt ( 1, reelQty.getReelQty() );
                    pstmt.setString ( 2, reelQty.getPackageName()  );
                    pstmt.setInt ( 3, Integer.parseInt(reelQty.getPinCount()) );


                    pstmt.executeUpdate();
                }
                else
                {
                    pstmt=conn.prepareStatement( "SELECT COUNT(*) CNT FROM REEL_QTY_TABLE WHERE  PACKAGE= ? AND PIN_COUNT= ?"  );
                    pstmt.setString ( 1, reelQty.getPackageName()  );
                    pstmt.setInt ( 2, Integer.parseInt(reelQty.getPinCount()) );

                    rset = pstmt.executeQuery();
                    boolean isExistAlready = false;
                    if ( rset.next() )
                    {
                        if ( rset.getInt ( "CNT" ) > 0 )
                        {
                            isExistAlready = true;
                        }
                    }
                    if ( !isExistAlready )
                    {
                        try { rset.close(); rset = null; } catch ( SQLException ignore ) {}
                        try { pstmt.close(); pstmt = null; } catch ( SQLException ignore ) {}
                        pstmt=conn.prepareStatement( "INSERT INTO REEL_QTY_TABLE ( PACKAGE, PIN_COUNT, REEL_QTY ) VALUES ( ?, ?, ? )" );
                        pstmt.setString ( 1, reelQty.getPackageName()  );
                        pstmt.setInt ( 2, Integer.parseInt(reelQty.getPinCount()) );
                        pstmt.setInt ( 3, reelQty.getReelQty() );

                        pstmt.executeUpdate();
                        reelQty.setExist( true );
                    }
                }
            }
            if (dbTransaction == null)
            {
                conn.commit();
            }
            success = true;
        }
        catch (Exception sqle)
        {
            throw new ScalarException ( "Could not update Reel Qty information", sqle );
        }

        finally
        {
            if (dbTransaction==null && conn != null && !success)
            {
                try{conn.rollback();}catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }

    public static int loadReelQty( DBTransaction dbTransaction, String packageType, int pinNumber ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        int reelQty                 = 0;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareCall( "SELECT REEL_QTY FROM REEL_QTY_TABLE WHERE PACKAGE= ? AND PIN_COUNT= ?" );

            pstmt.setString (1, packageType );
            pstmt.setInt(2, pinNumber );

            rs = pstmt.executeQuery();

            if( rs.next() )
            {
                reelQty = rs.getInt("REEL_QTY") ;
            }
        }
        catch ( SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not get REEL QUANTITY Details ", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return reelQty;
    }

    public static String loadContactPersonsEmails(DBTransaction dbTransaction, int releaseInfoSetupId) throws ScalarException
    {
       Connection conn         = null;
       PreparedStatement pstmt = null;
       ResultSet rs            = null;
       ArrayList emailsList    = new ArrayList();
       String[] emails         = null;
       String email            = null;

       try
       {
           if( dbTransaction == null )
           {
               conn = DataSource.getConnection();
           }
           else
           {
               conn = dbTransaction.getConnection();
           }

           pstmt = conn.prepareStatement( "SELECT Release_Info_Contact_person_Id, Contact_Person_Name, Contact_Person_Email FROM release_info_contact_person_details_table WHERE Release_Info_Setup_Id = ?" );

           pstmt.setInt    ( 1, releaseInfoSetupId );

           rs = pstmt.executeQuery();

           while ( rs.next() )
           {
               emailsList.add ( rs.getString ( "Contact_Person_Email" ) );
           }
           emails = ( String[] ) emailsList.toArray( new String[0] );
           email = StringUtils.concatStrings( emails, "," );
           email = StringUtils.removeDuplicateEmailIds ( email );

       }
       catch( Exception e)
       {
           throw new ScalarException ("Could not get the Release Info Contact persons Emails from the database.");
       }
       finally
       {
           if ( rs != null )
           {
               try { rs.close(); } catch (SQLException ignore) {}
           }

           if ( pstmt != null )
           {
               try { pstmt.close(); } catch (SQLException ignore) {}
           }

           if ( conn != null )
           {
               try { conn.close(); } catch (SQLException ignore) {}
           }
       }
       return email;
   }

   public static String loadVendorContactPersonsEmails( DBTransaction dbTransaction, int releaseInfoSetupId ) throws ScalarException
   {
       Connection conn         = null;
       PreparedStatement pstmt = null;
       ResultSet rs            = null;
       ArrayList emailsList    = new ArrayList();
       String[] emails         = null;
       String email            = null;

       try
       {
           if( dbTransaction == null )
           {
               conn = DataSource.getConnection();
           }
           else
           {
               conn = dbTransaction.getConnection();
           }

           pstmt = conn.prepareStatement( "SELECT DISTINCT PERSON_NAME, PERSON_EMAIL FROM RELEASE_INFO_VENDOR_CONTACT_PERSONS WHERE RELEASE_INFO_SETUP_ID = ? AND LEN(RTRIM(LTRIM(PERSON_NAME))) > 0 AND LEN(RTRIM(LTRIM(PERSON_EMAIL))) > 0" );

           pstmt.setInt    ( 1, releaseInfoSetupId );

           rs = pstmt.executeQuery();

           while ( rs.next() )
           {
               emailsList.add ( rs.getString ( "PERSON_EMAIL" ) );
           }
           emails = ( String[] ) emailsList.toArray( new String[0] );
           email = StringUtils.concatStrings( emails, "," );
           email = StringUtils.removeDuplicateEmailIds ( email );

       }
       catch( Exception e)
       {
           throw new ScalarException ("Could not get the Release Info Contact persons Emails from the database.");
       }
       finally
       {
           if ( rs != null )
           {
               try { rs.close(); } catch (SQLException ignore) {}
           }

           if ( pstmt != null )
           {
               try { pstmt.close(); } catch (SQLException ignore) {}
           }

           if ( conn != null )
           {
               try { conn.close(); } catch (SQLException ignore) {}
           }
       }
       return email;
   }


}